﻿using System;
using System.Collections.Generic;
using System.Data;
using System.Text;
using Wei8808Cms.Model;
using Wei8808Cms.Utility;

namespace Wei8808Cms.DAL
{
    public class NewsDAL : DbHelperBase
    {
        public int GetCount(NewsQcInfo qcmodel)
        {
            var strSql = new StringBuilder();
            strSql.Append(" SELECT COUNT(*) AS [Total] FROM News WHERE 1=1 ");
            var count = 0;
            using (var cmd = Db.GetSqlStringCommond(strSql.ToString()))
            {
                try
                {
                    using (IDataReader reader = Db.ExecuteReader(cmd))
                    {
                        if (reader.Read())
                        {
                            count = Convert.ToInt32(reader["Total"]);
                        }
                    }
                }
                catch (Exception ex)
                {
                    EventsLog.WriteLog("ErrorInfo" + ex.Message);
                }
                return count;
            }
        }

        public DataSet GetPageList(NewsQcInfo qcmodel)
        {
            var ds = new DataSet();
            var strSql = new StringBuilder();
            var start = qcmodel.PageSize * qcmodel.PageIndex + 1;
            var end = qcmodel.PageSize + start - 1;
            strSql.Append(" SELECT T.* FROM( ");
            strSql.Append(" SELECT row_number() over (order by A.CreateTime,A.IsTop) AS idx,A.[NewId],A.[NewTitle],A.[Author],A.[Source],A.[NewTypeId],B.[NewTypeName],A.[Click],A.[IsTop],A.[IsRec],A.[CreateTime] FROM [News] AS A INNER JOIN NewType AS B ON A.NewTypeId=B.NewTypeId ");
            strSql.Append(" WHERE 1=1 ");
            strSql.Append(" ) AS T ");
            strSql.Append(" Where T.idx between " + start + " and " + end);
            using (var cmd = Db.GetSqlStringCommond(strSql.ToString()))
            {
                try
                {
                    ds = Db.ExecuteDataSet(cmd);
                }
                catch (Exception ex)
                {
                    EventsLog.WriteLog("ErrorInfo" + ex.Message);
                }
            }
            return ds;
        }

        public bool Add(NewsInfo model)
        {
            var result = false;
            var strSql = new StringBuilder();
            strSql.Append(" INSERT INTO [News]([NewTitle],[Author],[Source],[MetaKeywords],[MetaDescription],[NewTypeId],[NewContent],[Click],[IsTop],[IsRec],[CreateUser],[CreateTime]) ");
            strSql.Append(" VALUES(@NewTitle,@Author,@Source,@MetaKeywords,@MetaDescription,@NewTypeId,@NewContent,@Click,@IsTop,@IsRec,@CreateUser,@CreateTime) ");
            using (var cmd = Db.GetSqlStringCommond(strSql.ToString()))
            {
                try
                {
                    Db.AddInParameter(cmd, "@NewTitle", model.NewTitle);
                    Db.AddInParameter(cmd, "@Author", model.Author);
                    Db.AddInParameter(cmd, "@Source", model.Source);
                    Db.AddInParameter(cmd, "@MetaKeywords", model.MetaKeywords);
                    Db.AddInParameter(cmd, "@MetaDescription", model.MetaDescription);
                    Db.AddInParameter(cmd, "@NewTypeId", model.NewTypeId);
                    Db.AddInParameter(cmd, "@NewContent", model.NewContent);
                    Db.AddInParameter(cmd, "@Click", model.Click);
                    Db.AddInParameter(cmd, "@IsTop", model.IsTop);
                    Db.AddInParameter(cmd, "@IsRec", model.IsRec);
                    Db.AddInParameter(cmd, "@CreateUser", model.CreateUser);
                    Db.AddInParameter(cmd, "@CreateTime", model.CreateTime);
                    result = Db.ExecuteNonQuery(cmd) > 0;
                }
                catch (Exception ex)
                {
                    EventsLog.WriteLog("ErrorInfo" + ex.Message);
                }
            }
            return result;
        }

        public bool Delete(List<NewsInfo> list)
        {
            var result = false;
            var strSql = new StringBuilder();
            strSql.Append(" DELETE FROM [News] WHERE NewId=@NewId ");
            using (var cmd = Db.GetSqlStringCommond(strSql.ToString()))
            {
                try
                {
                    Db.AddInParameter(cmd, "@Id", DbType.String);
                    foreach (var i in list)
                    {
                        cmd.Parameters["@NewId"].Value = i.NewId;
                        Db.ExecuteNonQuery(cmd);
                    }
                    result = true;
                }
                catch (Exception ex)
                {
                    EventsLog.WriteLog("ErrorInfo" + ex.Message);
                }
            }
            return result;
        }

        public NewsInfo GetNewsInfo(string id)
        {
            var model = new NewsInfo();
            object obj = DBNull.Value;
            var strSql = new StringBuilder();
            strSql.Append(" SELECT A.[NewId],A.[NewTitle],A.[Author],A.[Source],A.[MetaKeywords],A.[MetaDescription],A.[NewTypeId],B.[NewTypeName],A.[NewContent],A.[Click],A.[IsTop],A.[IsRec],A.[CreateTime] FROM [News] AS A INNER JOIN NewType AS B ON A.NewTypeId=B.NewTypeId where NewId=@NewId ");
            using (var cmd = Db.GetSqlStringCommond(strSql.ToString()))
            {
                try
                {
                    Db.AddInParameter(cmd, "@NewId", id);
                    using (var reader = Db.ExecuteReader(cmd))
                    {
                        if (reader.Read())
                        {
                            if (reader["NewId"] != DBNull.Value)
                                model.NewId = Convert.ToInt32(reader["NewId"]);
                            model.NewTitle = reader["NewTitle"] == obj ? null : Convert.ToString(reader["NewTitle"]);
                            model.Author = reader["Author"] == obj ? null : Convert.ToString(reader["Author"]);
                            model.Source = reader["Source"] == obj ? null : Convert.ToString(reader["Source"]);
                            model.MetaKeywords = reader["MetaKeywords"] == obj ? null : Convert.ToString(reader["MetaKeywords"]);
                            model.MetaDescription = reader["MetaDescription"] == obj ? null : Convert.ToString(reader["MetaDescription"]);
                            if (reader["NewTypeId"] != DBNull.Value)
                                model.NewTypeId = Convert.ToInt32(reader["NewTypeId"]);
                            model.NewContent = reader["NewContent"] == obj ? null : Convert.ToString(reader["NewContent"]);
                            //var content = reader["NewContent"] == obj ? null : Convert.ToString(reader["NewContent"]);
                            //if (content != null)
                            //{
                            //    model.NewContent = StringPlus.DropHTML(content);
                            //}
                            model.NewTypeName = reader["NewTypeName"] == obj ? null : Convert.ToString(reader["NewTypeName"]);
                            if (reader["Click"] != DBNull.Value)
                                model.Click = Convert.ToInt32(reader["Click"]);
                            if (reader["IsTop"] != DBNull.Value)
                                model.IsTop = Convert.ToInt32(reader["IsTop"]);
                            if (reader["IsRec"] != DBNull.Value)
                                model.IsRec = Convert.ToInt32(reader["IsRec"]);
                            if (reader["CreateTime"] != DBNull.Value)
                                model.CreateTime = Convert.ToDateTime(reader["CreateTime"]);
                        }
                    }
                }
                catch (Exception ex)
                {
                    EventsLog.WriteLog("ErrorInfo" + ex.Message);
                }
                return model;
            }
        }

        public bool Update(NewsInfo model)
        {
            var result = false;
            var strSql = new StringBuilder();
            strSql.Append(" UPDATE [News] SET [NewTitle] = @NewTitle,[Author] = @Author,[Source] = @Source,[MetaKeywords] = @MetaKeywords,[MetaDescription] = @MetaDescription,[NewTypeId] = @NewTypeId,[NewContent] = @NewContent,[Click] = @Click,[IsTop] = @IsTop,[IsRec] = @IsRec,[ModifyUser] = @ModifyUser,[ModifyTime] = @ModifyTime WHERE [NewId]=@NewId ");
            using (var cmd = Db.GetSqlStringCommond(strSql.ToString()))
            {
                try
                {
                    Db.AddInParameter(cmd, "@NewTitle", model.NewTitle);
                    Db.AddInParameter(cmd, "@Author", model.Author);
                    Db.AddInParameter(cmd, "@Source", model.Source);
                    Db.AddInParameter(cmd, "@MetaKeywords", model.MetaKeywords);
                    Db.AddInParameter(cmd, "@MetaDescription", model.MetaDescription);
                    Db.AddInParameter(cmd, "@NewTypeId", model.NewTypeId);
                    Db.AddInParameter(cmd, "@NewContent", model.NewContent);
                    Db.AddInParameter(cmd, "@Click", model.Click);
                    Db.AddInParameter(cmd, "@IsTop", model.IsTop);
                    Db.AddInParameter(cmd, "@IsRec", model.IsRec);
                    Db.AddInParameter(cmd, "@ModifyUser", model.ModifyUser);
                    Db.AddInParameter(cmd, "@ModifyTime", model.ModifyTime);
                    Db.AddInParameter(cmd, "@NewId", model.NewId);
                    if (Db.ExecuteNonQuery(cmd) > 0)
                    {
                        result = true;
                    }
                }
                catch (Exception ex)
                {
                    EventsLog.WriteLog("ErrorInfo" + ex.Message);
                }
            }
            return result;
        }

        public bool UpdateField(string id, string strField)
        {
            var result = false;
            var strSql = new StringBuilder();
            strSql.Append(" UPDATE News set " + strField);
            strSql.Append(" where NewId=@NewId ");
            using (var cmd = Db.GetSqlStringCommond(strSql.ToString()))
            {
                try
                {
                    Db.AddInParameter(cmd, "@NewId", id);
                    if (Db.ExecuteNonQuery(cmd) > 0)
                    {
                        result = true;
                    }
                }
                catch (Exception ex)
                {
                    EventsLog.WriteLog("ErrorInfo" + ex.Message);
                }
            }
            return result;
        }

        public DataTable GetModel()
        {
            var dt = new DataTable();
            var strSql = new StringBuilder();
            strSql.Append(" SELECT [NewId],[NewTitle],[NewContent],[NewTypeId] FROM [News] where IsRec=1 Order by IsTop,CreateTime ");
            using (var cmd = Db.GetSqlStringCommond(strSql.ToString()))
            {
                try
                {
                    dt = Db.ExecuteDataTable(cmd);
                }
                catch (Exception ex)
                {
                    EventsLog.WriteLog("ErrorInfo" + ex.Message);
                }
            }
            return dt;

            //var model = new NewsInfo();
            //object obj = DBNull.Value;
            //var strSql = new StringBuilder();
           
            //using (var cmd = Db.GetSqlStringCommond(strSql.ToString()))
            //{
            //    try
            //    {
            //        Db.AddInParameter(cmd, "@NewTypeId", newTypeId);
            //        using (var reader = Db.ExecuteReader(cmd))
            //        {
            //            if (reader.Read())
            //            {
            //                if (reader["NewId"] != DBNull.Value)
            //                    model.NewId = Convert.ToInt32(reader["NewId"]);
            //                model.NewTitle = reader["NewTitle"] == obj ? null : Convert.ToString(reader["NewTitle"]);
            //                var content = reader["NewContent"] == obj ? null : Convert.ToString(reader["NewContent"]);
            //                if (content != null)
            //                {
            //                    var strContent = StringPlus.DropHTML(content);
            //                    if (strContent.Length > 100)
            //                    {
            //                        model.NewContent = strContent.Substring(0, 100) + "...";
            //                    }
            //                    else
            //                    {
            //                        model.NewContent = strContent;
            //                    }
            //                }
            //                if (reader["NewTypeId"] != DBNull.Value)
            //                    model.NewTypeId = Convert.ToInt32(reader["NewTypeId"]);
            //            }
            //        }
            //    }
            //    catch (Exception ex)
            //    {
            //        EventsLog.WriteLog("ErrorInfo" + ex.Message);
            //    }
            //    return model;
            
        }

        public List<NewsInfo> GetNewsList(string id, int pageIndex, int pageSize)
        {
            var strSql = new StringBuilder();
            var list = new List<NewsInfo>();
            object obj = DBNull.Value;
            int start = (pageIndex - 1) * (pageSize + 1);
            int end = pageSize + start;
            if (!string.IsNullOrEmpty(id))
            {
                strSql.Append(" SELECT T.* FROM( ");
                strSql.Append(" SELECT row_number() over (order by A.IsTop,A.CreateTime desc) AS idx,A.[NewId],A.[NewTitle],A.[Author],A.[Source],A.[MetaKeywords],A.[MetaDescription],A.[NewTypeId],B.[NewTypeName],A.[NewContent],A.[Click],A.[CreateUser],A.[CreateTime] FROM [News] AS A ");
                strSql.Append(" INNER JOIN NewType AS B ON A.NewTypeId=B.NewTypeId ");
                strSql.Append(" WHERE A.[NewTypeId]=@NewTypeId ");
                strSql.Append(" ) AS T ");
                strSql.Append(" Where T.idx between " + start + " and " + end);
            }
            else
            {
                strSql.Append(" SELECT T.* FROM( ");
                strSql.Append(" SELECT row_number() over (order by A.IsTop,A.CreateTime desc) AS idx,A.[NewId],A.[NewTitle],A.[Author],A.[Source],A.[MetaKeywords],A.[MetaDescription],A.[NewTypeId],B.[NewTypeName],A.[NewContent],A.[Click],A.[CreateUser],A.[CreateTime] FROM [News] AS A ");
                strSql.Append(" INNER JOIN NewType AS B ON A.NewTypeId=B.NewTypeId ");
                strSql.Append(" ) AS T ");
                strSql.Append(" Where T.idx between " + start + " and " + end);
            }
            using (var cmd = Db.GetSqlStringCommond(strSql.ToString()))
            {
                try
                {
                    if (!string.IsNullOrEmpty(id))
                    {
                        Db.AddInParameter(cmd, "@NewTypeId", id);
                    }
                    using (IDataReader reader = Db.ExecuteReader(cmd))
                    {
                        while (reader.Read())
                        {
                            var model = new NewsInfo();
                            if (reader["NewId"] != DBNull.Value)
                                model.NewId = Convert.ToInt32(reader["NewId"]);
                            model.NewTitle = reader["NewTitle"] == obj ? null : Convert.ToString(reader["NewTitle"]);
                            model.Author = reader["Author"] == obj ? null : Convert.ToString(reader["Author"]);
                            model.Source = reader["Source"] == obj ? null : Convert.ToString(reader["Source"]);
                            model.MetaKeywords = reader["MetaKeywords"] == obj ? null : Convert.ToString(reader["MetaKeywords"]);
                            model.MetaDescription = reader["MetaDescription"] == obj ? null : Convert.ToString(reader["MetaDescription"]);
                            if (reader["NewTypeId"] != DBNull.Value)
                                model.NewTypeId = Convert.ToInt32(reader["NewTypeId"]);
                            model.MetaKeywords = reader["MetaKeywords"] == obj ? null : Convert.ToString(reader["MetaKeywords"]);
                            model.NewTypeName = reader["NewTypeName"] == obj ? null : Convert.ToString(reader["NewTypeName"]);
                            var content = reader["NewContent"] == obj ? null : Convert.ToString(reader["NewContent"]);
                            if (content != null)
                            {
                                var strContent = StringPlus.DropHTML(content);
                                if (strContent.Length > 100)
                                {
                                    model.NewContent = strContent.Substring(0, 100) + "...";
                                }
                                else
                                {
                                    model.NewContent = strContent;
                                }
                            }
                            if (reader["Click"] != DBNull.Value)
                                model.Click = Convert.ToInt32(reader["Click"]);
                            if (reader["CreateTime"] != DBNull.Value)
                                model.CreateTime = Convert.ToDateTime(reader["CreateTime"]);
                            list.Add(model);
                        }
                    }

                }
                catch (Exception ex)
                {
                    EventsLog.WriteLog("ErrorInfo" + ex.Message);
                }
            }
            return list;
        }

        public int GetTotal(string id)
        {
            var strSql = new StringBuilder();
            strSql.Append(!string.IsNullOrEmpty(id)
                              ? " SELECT COUNT(*) AS [Total] FROM News WHERE NewTypeId=@NewTypeId "
                              : " SELECT COUNT(*) AS [Total] FROM News  ");

            var count = 0;
            using (var cmd = Db.GetSqlStringCommond(strSql.ToString()))
            {
                try
                {
                    if (!string.IsNullOrEmpty(id))
                    {
                        Db.AddInParameter(cmd, "@NewTypeId", id);
                    }
                    using (IDataReader reader = Db.ExecuteReader(cmd))
                    {
                        if (reader.Read())
                        {
                            count = Convert.ToInt32(reader["Total"]);
                        }
                    }
                }
                catch (Exception ex)
                {
                    EventsLog.WriteLog("ErrorInfo" + ex.Message);
                }
                return count;
            }
        }
    }
}
